package com.hrhx.duhongming.util;

import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.apache.poi.hssf.converter.ExcelToHtmlConverter;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hwpf.HWPFDocument;
import org.apache.poi.hwpf.converter.PicturesManager;
import org.apache.poi.hwpf.converter.WordToHtmlConverter;
import org.apache.poi.hwpf.usermodel.Picture;
import org.apache.poi.hwpf.usermodel.PictureType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFPictureData;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.w3c.dom.Document;

import com.hrhx.duhongming.config.ConstantExcelPath;

public class PoiToHtmlUtil {
	/**
	 * 
	 * @param path 所在的文件夹
	 * @param inFileName 输入Word文档
	 * @param outFileName 输出Html文档
	 * @throws Throwable
	 */
	public static String wordToHtml(String path,String inFileName,String outFileName) throws Throwable {
		
		InputStream input = new FileInputStream(path + inFileName);
		HWPFDocument wordDocument = new HWPFDocument(input);
		WordToHtmlConverter wordToHtmlConverter = new WordToHtmlConverter(DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument());
		
		wordToHtmlConverter.setPicturesManager(new PicturesManager() {
			public String savePicture(byte[] content, PictureType pictureType,
					String suggestedName, float widthInches, float heightInches) {
				return suggestedName;
			}
		});
		
		wordToHtmlConverter.processDocument(wordDocument);
		List<Picture> pics = wordDocument.getPicturesTable().getAllPictures();
		if (pics != null) {
			for (int i = 0; i < pics.size(); i++) {
				Picture pic = (Picture) pics.get(i);
				try {
					pic.writeImageContent(new FileOutputStream(path
							+ pic.suggestFullFileName()));
				} catch (FileNotFoundException e) {
					e.printStackTrace();
				}
			}
		}
		
		Document htmlDocument = wordToHtmlConverter.getDocument();
		ByteArrayOutputStream outStream = new ByteArrayOutputStream();
		DOMSource domSource = new DOMSource(htmlDocument);
		StreamResult streamResult = new StreamResult(outStream);

		TransformerFactory tf = TransformerFactory.newInstance();
		Transformer serializer = tf.newTransformer();
		serializer.setOutputProperty(OutputKeys.ENCODING, "gb2312");
		serializer.setOutputProperty(OutputKeys.INDENT, "yes");
		serializer.setOutputProperty(OutputKeys.METHOD, "html");
		serializer.transform(domSource, streamResult);
		outStream.close();

		return new String(outStream.toByteArray());

		//FileUtils.write(new File(path, outFileName), content, "gb2312");
	}
	
	/**
	 * 
	 * @param path 所在的文件夹
	 * @param inFileName 输入Excel文档
	 * @param outFileName 输出Html文档
	 * @throws IOException 
	 * @throws ParserConfigurationException 
	 * @throws TransformerException 
	 * @throws Throwable
	 */
	@SuppressWarnings("rawtypes")
	public static String  excel2003ToHtml(String destFileName) throws IOException, ParserConfigurationException, TransformerException{
		
		InputStream input = new FileInputStream(destFileName);
		HSSFWorkbook excelBook = new HSSFWorkbook(input);
		resetCellFormula(excelBook);
		ExcelToHtmlConverter excelToHtmlConverter = new ExcelToHtmlConverter(DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument());
		
		//去掉Excel头行
		excelToHtmlConverter.setOutputColumnHeaders(false);
		//去掉Excel行号
		excelToHtmlConverter.setOutputRowNumbers(false);
		
		excelToHtmlConverter.processWorkbook(excelBook);
		List pics = excelBook.getAllPictures();
		if (pics != null) {
			for (int i = 0; i < pics.size(); i++) {
				Picture pic = (Picture) pics.get(i);
				try {
					pic.writeImageContent(new FileOutputStream(ConstantExcelPath.getExcelTempPath()
							+ pic.suggestFullFileName()));
				} catch (FileNotFoundException e) {
					e.printStackTrace();
				}
			}
		}
		
		Document htmlDocument = excelToHtmlConverter.getDocument();

		ByteArrayOutputStream outStream = new ByteArrayOutputStream();
		DOMSource domSource = new DOMSource(htmlDocument);
		StreamResult streamResult = new StreamResult(outStream);
		TransformerFactory tf = TransformerFactory.newInstance();
		Transformer serializer = tf.newTransformer();
		
		//判断操作系统是windows，还是Linux的SUSE系统
		if(isWindows()){
			serializer.setOutputProperty(OutputKeys.ENCODING, "gb2312");
		}else{
			serializer.setOutputProperty(OutputKeys.ENCODING, "utf-8");
		}
		
		serializer.setOutputProperty(OutputKeys.INDENT, "yes");
		serializer.setOutputProperty(OutputKeys.METHOD, "html");
		
		serializer.transform(domSource, streamResult);
		outStream.close();
		return new String(outStream.toByteArray());
	}
	
	/**
	 * 
	 * @param path 所在的文件夹
	 * @param inFileName 输入Excel文档
	 * @param outFileName 输出Html文档
	 * @throws IOException 
	 * @throws ParserConfigurationException 
	 * @throws TransformerException 
	 * @throws Throwable
	 */
	public static String  excel2007ToHtml(String destFileName) throws IOException, ParserConfigurationException, TransformerException{
		
		InputStream input = new FileInputStream(destFileName);
		XSSFWorkbook excelBook = new XSSFWorkbook(input);
		resetCellFormula(excelBook);
		ExcelToHtmlConverter excelToHtmlConverter = new ExcelToHtmlConverter(DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument());
		
		//去掉Excel头行
		excelToHtmlConverter.setOutputColumnHeaders(false);
		//去掉Excel行号
		excelToHtmlConverter.setOutputRowNumbers(false);
		
		//excelToHtmlConverter.processWorkbook(excelBook);
		List<XSSFPictureData> pics = excelBook.getAllPictures();
		if (pics != null) {
			for (int i = 0; i < pics.size(); i++) {
//				XSSFPictureData pic = (XSSFPictureData) pics.get(i);
//				try {
//					pic.writeImageContent(new FileOutputStream(ConstantExcelPath.getExcelTempPath()
//							+ pic.suggestFullFileName()));
//				} catch (FileNotFoundException e) {
//					e.printStackTrace();
//				}
			}
		}
		
		Document htmlDocument = excelToHtmlConverter.getDocument();

		ByteArrayOutputStream outStream = new ByteArrayOutputStream();
		DOMSource domSource = new DOMSource(htmlDocument);
		StreamResult streamResult = new StreamResult(outStream);
		TransformerFactory tf = TransformerFactory.newInstance();
		Transformer serializer = tf.newTransformer();
		
		//判断操作系统是windows，还是Linux的SUSE系统
		if(isWindows()){
			serializer.setOutputProperty(OutputKeys.ENCODING, "gb2312");
		}else{
			serializer.setOutputProperty(OutputKeys.ENCODING, "utf-8");
		}
		
		serializer.setOutputProperty(OutputKeys.INDENT, "yes");
		serializer.setOutputProperty(OutputKeys.METHOD, "html");
		
		serializer.transform(domSource, streamResult);
		outStream.close();
		return new String(outStream.toByteArray());
	}
	
	public static boolean isWindows(){
	    boolean flag = false;
	    if (System.getProperties().getProperty("os.name").toUpperCase().indexOf("WINDOWS") != -1) {
	    flag = true;
	    }
	    return flag;
	}
	
	/**
	 * 
	 * 重新设置单元格计算公式
	 * 
	 * */
	public static void resetCellFormula(HSSFWorkbook wb) {
		HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(wb);
		int sheetNum = wb.getNumberOfSheets();
		for (int i = 0; i < sheetNum; i++) {
			HSSFSheet sheet = wb.getSheetAt(i);
			int rows = sheet.getLastRowNum() + 1;
			for (int j = 0; j < rows; j++) {
				HSSFRow row = sheet.getRow(j);
				if (row == null)
					continue;
				int cols = row.getLastCellNum();
				for (int k = 0; k < cols; k++) {
					HSSFCell cell = row.getCell(k);
					if(cell!=null)
					   cell.getCellType();
					   //System.out.println("cell["+j+","+k+"]=:"+cell.getCellType());
					if (cell == null)
						continue;
					if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
						cell.setCellFormula(cell.getCellFormula());
						cell.getCellFormula();
						//System.out.println("----公式："+cell.getCellFormula());
						cell=e.evaluateInCell(cell);
						//System.out.println("-----------"+cell.getNumericCellValue());
					}
				}
			}
		}
	}
	/**
	 * 
	 * 重新设置单元格计算公式
	 * 
	 * */
	public static void resetCellFormula(XSSFWorkbook wb) {
		XSSFFormulaEvaluator e = new XSSFFormulaEvaluator(wb);
		int sheetNum = wb.getNumberOfSheets();
		for (int i = 0; i < sheetNum; i++) {
			XSSFSheet sheet = wb.getSheetAt(i);
			int rows = sheet.getLastRowNum() + 1;
			for (int j = 0; j < rows; j++) {
				XSSFRow row = sheet.getRow(j);
				if (row == null)
					continue;
				int cols = row.getLastCellNum();
				for (int k = 0; k < cols; k++) {
					XSSFCell cell = row.getCell(k);
					if(cell!=null)
					   cell.getCellType();
					   //System.out.println("cell["+j+","+k+"]=:"+cell.getCellType());
					if (cell == null)
						continue;
					if (cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) {
						cell.setCellFormula(cell.getCellFormula());
						cell.getCellFormula();
						//System.out.println("----公式："+cell.getCellFormula());
						cell=e.evaluateInCell(cell);
						//System.out.println("-----------"+cell.getNumericCellValue());
					}
				}
			}
		}
	}
}
